from flask import Flask, request, jsonify
import pymysql
from pymysql.cursors import DictCursor

app = Flask(__name__)

# 数据库配置
DB_CONFIG = {
    'host': '172.16.10.155',
    'user': 'user',
    'password': 'Qwe!123456',
    'database': 'ruoyi-nbcio',
    'charset': 'utf8mb4'
}

@app.route('/execute', methods=['POST'])
def execute_sql():
    """
    执行SQL接口
    POST参数: {"sql": "SELECT * FROM users"}
    """
    try:
        # 获取SQL语句
        data = request.get_json()
        if not data or 'sql' not in data:
            return jsonify({'error': 'Missing SQL parameter'}), 400
        
        sql = data['sql'].strip()
        
        # 简单SQL校验（生产环境需要更严格的检查）
        if not sql.lower().startswith(('select', 'insert', 'update', 'delete')):
            return jsonify({'error': 'Only SELECT/INSERT/UPDATE/DELETE allowed'}), 403
        
        # 连接数据库
        connection = pymysql.connect(**DB_CONFIG)
        
        with connection.cursor(DictCursor) as cursor:
            cursor.execute(sql)
            
            # 处理不同SQL类型返回结果
            if sql.lower().startswith('select'):
                result = cursor.fetchall()
            else:
                connection.commit()
                result = {'affected_rows': cursor.rowcount}
                
        return  result
        
    except pymysql.Error as e:
        return jsonify({'error': f'Database error: {str(e)}'}), 500
    except Exception as e:
        return jsonify({'error': f'Server error: {str(e)}'}), 500
    finally:
        if 'connection' in locals():
            connection.close()

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)